package dao;

import com.alibaba.fastjson.JSON;


import domin.Book;
import domin.BookCategory;
import domin.BookStatus;
import domin.Library;
import lombok.extern.slf4j.Slf4j;

import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;
import java.util.TreeSet;

@Slf4j
public class BookDao {
    private static BookDao bookDao= new BookDao();
    public BookDao(){}
    public static BookDao getInstance(){
        return bookDao;
    }
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    public Book find (Connection connection, int id) throws SQLException {
        Book book = null;
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM book WHERE id=?");
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            book= new Book();
            book.setId(resultSet.getInt("id"));
            book.setName(resultSet.getString("name"));
            book.setAuthor(resultSet.getString("author"));
            BookStatus bookStauts = BookStautsDao.getInstance().find(connection,resultSet.getInt("status"));
            book.setStatus(bookStauts);
            BookCategory bookCategory = BookCategoryDao.getInstance().find(connection,resultSet.getInt("bookCategory"));
            book.setBookCategory(bookCategory);
            Library location = LibraryDao.getInstance().find(connection,resultSet.getInt("location"));
            book.setLocation(location);
            book.setQuantitiesInLibrary(resultSet.getInt("quantitiesInLibrary"));
            book.setStorageTime(resultSet.getDate("storageTime"));
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return book;
    }
    public Collection<Book> findAll(Connection connection, Collection<Condition> condition, Pagination pagination) throws SQLException {
        Collection<Book> books = new TreeSet<Book>();
        int totalNum = CommonDao.getInstance().count(connection,"book");
        String sql_select_all = "SELECT * FROM book ";
        if (condition != null){
            sql_select_all += Condition.toWhereClause(JSON.toJSONString(condition));
            log.trace(sql_select_all);
        }
        if (pagination != null){
            //如果带有分页请求，则根据请求创建对应的LIMIT子句
            sql_select_all += pagination.toLimitClause(totalNum);
            log.trace(sql_select_all);
        }
        this.preparedStatement = connection.prepareStatement(sql_select_all);
        this.resultSet = preparedStatement.executeQuery();
        while (this.resultSet.next()){
            Book book = new Book();
            book= new Book();
            book.setId(resultSet.getInt("id"));
            book.setName(resultSet.getString("name"));
            book.setAuthor(resultSet.getString("author"));
            BookStatus bookStauts = BookStautsDao.getInstance().find(connection,resultSet.getInt("status"));
            book.setStatus(bookStauts);
            BookCategory bookCategory = BookCategoryDao.getInstance().find(connection,resultSet.getInt("bookCategory"));
            book.setBookCategory(bookCategory);
            Library location = LibraryDao.getInstance().find(connection,resultSet.getInt("location"));
            book.setLocation(location);
            book.setQuantitiesInLibrary(resultSet.getInt("quantitiesInLibrary"));
            book.setStorageTime(resultSet.getDate("storageTime"));
            books.add(book);
        }
        JdbcHelper.close(this.resultSet,this.preparedStatement);
        return books;
    }

    public  Book find(Connection connection, Integer id) throws SQLException {
        Book book = new Book();
        this.preparedStatement = connection.prepareStatement("select * from book where id=" + id);
        this.resultSet = this.preparedStatement.executeQuery();
        if (resultSet.next()){
            book= new Book();
            book.setId(resultSet.getInt("id"));
            book.setName(resultSet.getString("name"));
            book.setAuthor(resultSet.getString("author"));
            BookStatus bookStauts = BookStautsDao.getInstance().find(connection,resultSet.getInt("status"));
            book.setStatus(bookStauts);
            BookCategory bookCategory = BookCategoryDao.getInstance().find(connection,resultSet.getInt("bookCategory"));
            book.setBookCategory(bookCategory);
            Library location = LibraryDao.getInstance().find(connection,resultSet.getInt("location"));
            book.setLocation(location);
            book.setQuantitiesInLibrary(resultSet.getInt("quantitiesInLibrary"));
            book.setStorageTime(resultSet.getDate("storageTime"));
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return book;
    }
    public Collection<Book> getAllWithCondition(Connection connection,String s) throws SQLException{
        Collection<Book> books = new HashSet<Book>();
        String value = "%" + s + "%";
        value=" \"" + value + "\"";
        PreparedStatement preparedStatement = connection.prepareStatement("" +
                "select * from book where " +

                "name like " + value

                +" OR author like" + value

                +" OR bookCategory IN (SELECT id FROM bookCategory WHERE category like "+value+")"
        );
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
             Book book= new Book();
                book.setId(resultSet.getInt("id"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                BookStatus bookStauts = BookStautsDao.getInstance().find(connection,resultSet.getInt("status"));
                book.setStatus(bookStauts);
                BookCategory bookCategory = BookCategoryDao.getInstance().find(connection,resultSet.getInt("bookCategory"));
                book.setBookCategory(bookCategory);
                Library location = LibraryDao.getInstance().find(connection,resultSet.getInt("location"));
                book.setLocation(location);
                book.setQuantitiesInLibrary(resultSet.getInt("quantitiesInLibrary"));
            books.add(book);
        }
        JdbcHelper.close(null,preparedStatement);
        return books;
    }

    public boolean update(Connection connection,Book book) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("update book set name=?,author=?,bookCategory=?,location=?,storageTime=?,quantitiesInLibrary=?,Status=? " +
                "where id=?");
        preparedStatement.setString(1,book.getName());
        preparedStatement.setString(2,book.getAuthor());
        preparedStatement.setInt(3,book.getBookCategory().getId());
        preparedStatement.setInt(4,book.getLocation().getId());
        preparedStatement.setDate(5,book.getStorageTime());
        preparedStatement.setInt(6,book.getQuantitiesInLibrary());
        preparedStatement.setInt(7,book.getStatus().getId());
        preparedStatement.setInt(8,book.getId());
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return rowAffected > 0;
    }

    public boolean add(Connection connection , Book book) throws SQLException {
        PreparedStatement preparedStatement =
                connection.prepareStatement(
                        "INSERT INTO " +
                                "book (name,author,bookCategory,location,storageTime,quantitiesInLibrary,status)" +
                                "VALUES(?,?,?,?,?,?,?)"
                );
        preparedStatement.setString(1,book.getName());
        preparedStatement.setString(2,book.getAuthor());
        preparedStatement.setInt(3,book.getBookCategory().getId());
        preparedStatement.setInt(4,book.getLocation().getId());
        preparedStatement.setDate(5,book.getStorageTime());
        preparedStatement.setInt(6,book.getQuantitiesInLibrary());
        preparedStatement.setInt(7,book.getStatus().getId());
        int rowAffected = preparedStatement.executeUpdate();

        JdbcHelper.close(null,preparedStatement);
        return rowAffected>0;
    }

    public boolean delete(Connection connection,Integer id) throws SQLException {
        this.preparedStatement = connection.prepareStatement("delete from book where id=" + id);
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return rowAffected>0;
    }

}
